package com.bw.mapper;

import com.bw.bean.Movie;
import com.bw.bean.Movie1;
import com.bw.bean.Movie2;
import com.bw.bean.Movie3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface MovieMapper {

    @Select("SELECT bp.name , sum(oi.final_total_amount)'value' from order_info oi LEFT JOIN base_province bp ON oi.province_id = bp.Id GROUP BY bp.name;")
    List<Movie> getList1();

    @Select("SELECT bt.name , SUM(od.order_price*od.sku_num) 'value' from  order_detail od LEFT JOIN sku_info si  ON si.id = od.sku_id \n" +
            "LEFT JOIN base_trademark bt ON si.tm_id = bt.id GROUP BY bt.name ORDER BY SUM(od.order_price*od.sku_num) DESC;")
    List<Movie> getList2();

    @Select("select '用户数(人)' name,a.num1,b.num2 from\n" +
            "(select  count(*) num1  from  user_info where timestampdiff(year,create_time,now())<1) a,\n" +
            "(select  count(*) num2  from  user_info where timestampdiff(year,create_time,now())>=1)b;  ")
    List<Movie1> getList();


    @Select("select bc1.name,sum(oi.final_total_amount) 'value'\n" +
            "from order_info oi\n" +
            "         left join order_detail od on oi.id = od.order_id\n" +
            "         left join sku_info si on od.sku_id = si.id\n" +
            "         left join base_category3 b on si.category3_id = b.id\n" +
            "         left join  base_category2 c on b.category2_id = c.id\n" +
            "         left join base_category1 bc1 on c.category1_id = bc1.id\n" +
            "group by bc1.name;")
    List<Movie> getList3();


    @Select("select '用户数(人)' name,a.num1,b.num2 from\n" +
            "(select  count(*) num1  from  user_info where timestampdiff(year,create_time,now())<1) a,\n" +
            "(select  count(*) num2  from  user_info where timestampdiff(year,create_time,now())>=1) b;")
    List<Movie1> getList4();

    @Select("SELECT\n" +
            "b2.name,\n" +
            "SUM(od.order_price*od.sku_num) AS 'value'\n" +
            "FROM  order_detail od\n" +
            "LEFT JOIN sku_info si  ON  si.id=od.sku_id\n" +
            "LEFT JOIN base_category3 b3 ON b3.id =si.category3_id\n" +
            "LEFT JOIN base_category2 b2 ON b2.id=b3.category2_id\n" +
            "LEFT JOIN base_category1 b1 ON b1.id=b2.category1_id\n" +
            "GROUP BY  b2.name\n" +
            "ORDER BY  SUM(od.order_price*od.sku_num) DESC;")
    List<Movie> getList5();

    @Select("SELECT concat(substr(ui.birthday,3,1),'0后') name,sum(oi.final_total_amount) value from order_info oi\n" +
            "left join user_info ui on oi.user_id = ui.id\n" +
            "GROUP BY concat(substr(ui.birthday,3,1),'0后');")
    List<Movie> getList6();

    @Select("SELECT yys name,sum(final_total_amount) value from order_info where yys is not null group by yys;")
    List<Movie> getList7();

    @Select("SELECT bt.name , ui.gender gender, sum(oi.final_total_amount) value from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on od.sku_id = si.id\n" +
            "left join base_trademark bt on si.tm_id = bt.id\n" +
            "left join user_info ui on oi.user_id = ui.id\n" +
            "GROUP BY bt.name , ui.gender;")
    List<Movie2> getList8();


    @Select("SELECT\n" +
            "    c.name,\n" +
            "    SUM(oi.final_total_amount) value\n" +
            "FROM order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on od.sku_id = si.id\n" +
            "left join ds1.base_category3 b on b.id = si.category3_id\n" +
            "left join ds1.base_category2 c on c.id = b.category2_id\n" +
            "group by c.name;")
    List<Movie> getList9();


    @Select("SELECT\n" +
            "    c.name,\n" +
            "    SUM(oi.final_total_amount) value\n" +
            "FROM order_info oi\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on od.sku_id = si.id\n" +
            "left join ds1.base_category3 b on b.id = si.category3_id\n" +
            "left join ds1.base_category2 c on c.id = b.category2_id\n" +
            "group by c.name\n" +
            "order by SUM(oi.final_total_amount) desc;")
    List<Movie> getList10();


    @Select("SELECT   SUM(final_total_amount) FROM  order_info;")
    Double getList11();


    @Select("SELECT\n" +
            "    si.spu_name name,\n" +
            "    SUM(oi.final_total_amount) value\n" +
            "FROM   order_detail  od\n" +
            "           LEFT  JOIN  order_info  oi ON  od.order_id= oi.id\n" +
            "           LEFT  JOIN   sku_info  sk ON   od.sku_id=sk.id\n" +
            "           LEFT  JOIN   spu_info si  ON   sk.spu_id=si.id\n" +
            "GROUP  BY  si.spu_name\n" +
            "ORDER BY  SUM(od.sku_num*od.order_price) DESC;")
    List<Movie3> getList12();
}
